﻿using Dapper;
using Domain.Entity;
using Domain.Entity.sys;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class DictTypeRepository : IDictTypeRepository
    {

        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        //添加字典类型信息
        public async Task<int> AddDictType(DictType dictType)
        {
            using (SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[sys_dict_type](dict_typename, dict_status, create_name, create_time,remark)
                                values('{dictType.dict_typename}',{dictType.dict_status},'{dictType.create_name}','{dictType.create_time}','{dictType.remark}')";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //删除字典类型信息
        public async Task<int> DeleteDictType(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[sys_dict_type] set is_delete=1 where dict_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //分页查询字典类型信息
        public async Task<FenYe<DictType>> GetDictType(FenYe<DictType> fenYe, string? name, int? status, string? createname, string? starttime, string? endtime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "dict_id");
                param.Add("@tabelName", "sys_dict_type");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(name))
                    sqlparam += $" and dict_typename like '%{name}%'";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and create_name like '%{createname}%'";
                if (status != null && status != 0)
                    sqlparam += $" and  dict_status={status}";
                if (!string.IsNullOrEmpty(starttime) || !string.IsNullOrEmpty(endtime))
                    sqlparam += $" and  create_time between '{starttime}' and '{endtime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<DictType> list = sqlConnection.Query<DictType>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //根据id查询字典类型信息
        public async Task<DictType> GetDictTypeById(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[sys_dict_type] where dict_id={id}";
                return await sqlConnection.QueryFirstAsync<DictType>(sql);
            }
        }

        //修改字典类型信息
        public async Task<int> UPdateDictType(DictType dictType)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[sys_dict_type] set dict_typename='{dictType.dict_typename}',dict_status='{dictType.dict_status}',update_name='{dictType.update_name}',update_time='{dictType.update_time}',remark='{dictType.remark}' where dict_id={dictType.dict_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //查询所有字典类型
        public async Task<List<DictType>> GetType()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select dict_id,dict_typename from [dbo].[sys_dict_type] where is_delete=0";
                return sqlConnection.Query<DictType>(sql).ToList();
            }
        }
    }
}
